# 数据库服务层 - 基于SQLAlchemy的数据访问实现
import math
from typing import Optional, List

from sqlalchemy import and_, or_, desc, func

from config.database import get_db_session
from config.logging_config import get_logger
# 导入导航保存服务
from navigation.saveNavigation import NavigationSaveService
from pojo.ai_web_api_calling import AIWebApiCallingQueryParams, AIWebApiCallingListResponse, AIWebApiCallingCreate, \
    AIWebApiCallingResponse, AIWebApiCallingUpdate, AIWebApiCallingPathUpdate
from pojo.ai_web_api_calling_model import AIWebApiCallingSearchRequest, AIWebApiCallingModel

# 设置日志记录器
logger = get_logger(__name__)


class AIWebApiCallingService:
    """页面导航路径规划数据服务"""

    @staticmethod
    async def get_list(query_params: AIWebApiCallingQueryParams, page_num: int = 1,
                       page_size: int = 20) -> AIWebApiCallingListResponse:
        """获取导航页面列表"""

        db_session = next(get_db_session())
        try:
            logger.info(f"获取导航页面列表 - 页码: {page_num}, 页大小: {page_size}, 查询参数: {query_params}")

            # 构建查询
            query = db_session.query(AIWebApiCallingModel)

            # 应用筛选条件
            filters = []

            # 场景ID筛选
            if query_params.scene_id:
                filters.append(AIWebApiCallingModel.scene_id == query_params.scene_id)

            # 页面名称模糊查询
            if query_params.page_name:
                filters.append(AIWebApiCallingModel.page_name.like(f"%{query_params.page_name}%"))

            # 导航路径模糊查询
            if query_params.navigation_path:
                filters.append(AIWebApiCallingModel.navigation_path.like(f"%{query_params.navigation_path}%"))

            # 应用所有筛选条件
            if filters:
                query = query.filter(and_(*filters))

            # 获取总数量
            total = query.count()

            # 分页查询
            offset = (page_num - 1) * page_size
            items_db = query.order_by(desc(AIWebApiCallingModel.created_at)).offset(offset).limit(page_size).all()

            # 转换为响应格式
            items = []
            for item_db in items_db:
                items.append(AIWebApiCallingResponse(
                    page_id=item_db.page_id,
                    scene_id=item_db.scene_id,
                    tenant_id=item_db.tenant_id,
                    page_name=item_db.page_name,
                    page_tab=item_db.get_page_tab(),
                    function_desc_list=item_db.get_function_desc_list(),
                    operation_desc_list=item_db.get_operation_desc_list(),
                    navigation_path=item_db.navigation_path,
                    prev_path_list=item_db.get_prev_path_list(),
                    next_path_list=item_db.get_next_path_list(),
                    mcp_list=item_db.get_mcp_list() if hasattr(item_db, 'get_mcp_list') else [],
                    is_deleted=item_db.is_deleted,
                    created_by=item_db.created_by,
                    created_at=item_db.created_at,
                    updated_by=item_db.updated_by,
                    updated_at=item_db.updated_at
                ))

            logger.info(f"成功获取导航页面列表，总数: {total}, 当前页数量: {len(items)}")
            return AIWebApiCallingListResponse(total=total, items=items)

        except Exception as e:
            logger.error(f"查询导航页面列表失败 - 页码: {page_num}, 页大小: {page_size}, 查询参数: {query_params}",
                         exc_info=True)
            raise Exception(f"查询导航页面列表失败: {str(e)}") from e
        finally:
            db_session.close()

    @staticmethod
    async def get_by_id(page_id: str) -> Optional[AIWebApiCallingResponse]:
        """根据ID获取导航页面"""

        db_session = next(get_db_session())
        try:
            logger.info(f"根据ID获取导航页面: {page_id}")

            # 查询数据库
            item_db = db_session.query(AIWebApiCallingModel).filter(
                and_(
                    AIWebApiCallingModel.page_id == page_id,
                    AIWebApiCallingModel.is_deleted == 0
                )
            ).first()

            if not item_db:
                logger.warning(f"导航页面不存在: {page_id}")
                return None

            # 转换为响应格式
            result = AIWebApiCallingResponse(
                page_id=item_db.page_id,
                scene_id=item_db.scene_id,
                tenant_id=item_db.tenant_id,
                page_name=item_db.page_name,
                page_tab=item_db.get_page_tab(),
                function_desc_list=item_db.get_function_desc_list(),
                operation_desc_list=item_db.get_operation_desc_list(),
                navigation_path=item_db.navigation_path,
                prev_path_list=item_db.get_prev_path_list(),
                next_path_list=item_db.get_next_path_list(),
                mcp_list=item_db.get_mcp_list() if hasattr(item_db, 'get_mcp_list') else [],
                is_deleted=item_db.is_deleted,
                created_by=item_db.created_by,
                created_at=item_db.created_at,
                updated_by=item_db.updated_by,
                updated_at=item_db.updated_at
            )

            logger.info(f"成功获取导航页面: {page_id}")
            return result

        except Exception as e:
            logger.error(f"根据ID查询导航页面失败 - ID: {page_id}", exc_info=True)
            raise Exception(f"根据ID查询导航页面失败: {str(e)}") from e
        finally:
            db_session.close()

    @staticmethod
    async def export_data(scene_ids: Optional[List[str]] = None, include_deleted: bool = False) -> List[dict]:
        """导出导航数据"""

        db_session = next(get_db_session())
        try:
            logger.info(f"导出导航数据 - 场景IDs: {scene_ids}, 包含已删除: {include_deleted}")

            # 构建查询
            query = db_session.query(AIWebApiCallingModel)

            # 场景ID筛选
            if scene_ids:
                query = query.filter(AIWebApiCallingModel.scene_id.in_(scene_ids))

            # 是否包含已删除数据
            if not include_deleted:
                query = query.filter(AIWebApiCallingModel.is_deleted == 0)

            # 按创建时间排序
            query = query.order_by(AIWebApiCallingModel.created_at)

            # 获取所有数据
            items = query.all()

            # 转换为导出格式
            export_data = []
            for item in items:
                page_tab_str = ",".join(item.get_page_tab()) if item.get_page_tab() else ""
                function_desc_str = ",".join(item.get_function_desc_list()) if item.get_function_desc_list() else ""
                operation_desc_str = ",".join(item.get_operation_desc_list()) if item.get_operation_desc_list() else ""
                prev_path_str = ",".join(item.get_prev_path_list()) if item.get_prev_path_list() else ""
                next_path_str = ",".join(item.get_next_path_list()) if item.get_next_path_list() else ""
                mcp_list_str = ",".join(item.get_mcp_list()) if hasattr(item, 'get_mcp_list') and item.get_mcp_list() else ""

                export_data.append({
                    "页面ID": item.page_id,
                    "场景ID": item.scene_id,
                    "租户ID": item.tenant_id,
                    "页面名称": item.page_name,
                    "页面标签页": page_tab_str,
                    "功能描述": function_desc_str,
                    "操作描述": operation_desc_str,
                    "导航路径": item.navigation_path,
                    "前置路径": prev_path_str,
                    "后置路径": next_path_str,
                    "MCP工具列表": mcp_list_str,
                    "删除标记": "已删除" if item.is_deleted == 1 else "正常",
                    "创建者": item.created_by,
                    "创建时间": item.created_at.strftime("%Y-%m-%d %H:%M:%S") if item.created_at else "",
                    "更新者": item.updated_by or "",
                    "更新时间": item.updated_at.strftime("%Y-%m-%d %H:%M:%S") if item.updated_at else ""
                })

            logger.info(f"成功导出导航数据，数量: {len(export_data)}")
            return export_data

        except Exception as e:
            logger.error(f"导出导航数据失败 - 场景IDs: {scene_ids}, 包含已删除: {include_deleted}", exc_info=True)
            raise Exception(f"导出导航数据失败: {str(e)}") from e
        finally:
            db_session.close()

    @staticmethod
    async def search(search_request: AIWebApiCallingSearchRequest) -> dict:
        """搜索导航页面"""

        db_session = next(get_db_session())
        try:
            logger.info(f"搜索导航页面 - 关键词: {search_request.keyword}, 场景IDs: {search_request.scene_ids}")

            # 构建查询
            query = db_session.query(AIWebApiCallingModel)

            # 构建搜索条件
            filters = []

            # 关键词搜索（支持页面名称和导航路径）
            if search_request.keyword:
                keyword_filter = or_(
                    AIWebApiCallingModel.page_name.like(f"%{search_request.keyword}%"),
                    AIWebApiCallingModel.navigation_path.like(f"%{search_request.keyword}%")
                )
                filters.append(keyword_filter)

            # 场景ID列表筛选
            if search_request.scene_ids:
                filters.append(AIWebApiCallingModel.scene_id.in_(search_request.scene_ids))

            # 路径包含关键词
            if search_request.path_contains:
                filters.append(AIWebApiCallingModel.navigation_path.like(f"%{search_request.path_contains}%"))

            # 创建时间范围筛选
            if search_request.created_date_start:
                filters.append(AIWebApiCallingModel.created_at >= search_request.created_date_start)

            if search_request.created_date_end:
                filters.append(AIWebApiCallingModel.created_at <= search_request.created_date_end)

            # 默认不查询已删除的数据
            filters.append(AIWebApiCallingModel.is_deleted == 0)

            # 应用筛选条件
            if filters:
                query = query.filter(and_(*filters))

            # 功能描述关键词筛选 (JSON字段搜索)
            if search_request.function_keywords:
                for keyword in search_request.function_keywords:
                    # 使用JSON搜索，这里简化为like查询
                    query = query.filter(
                        func.JSON_SEARCH(AIWebApiCallingModel.function_desc_list, 'one', f'%{keyword}%').isnot(None)
                    )

            # 操作描述关键词筛选 (JSON字段搜索)
            if search_request.operation_keywords:
                for keyword in search_request.operation_keywords:
                    # 使用JSON搜索，这里简化为like查询
                    query = query.filter(
                        func.JSON_SEARCH(AIWebApiCallingModel.operation_desc_list, 'one', f'%{keyword}%').isnot(None)
                    )

            # 获取总数量
            total = query.count()

            # 计算总页数
            total_pages = math.ceil(total / search_request.page_size) if total > 0 else 0

            # 分页查询
            offset = (search_request.page_num - 1) * search_request.page_size
            items_db = query.order_by(desc(AIWebApiCallingModel.created_at)).offset(offset).limit(
                search_request.page_size).all()

            # 转换为字典格式
            items = []
            for item_db in items_db:
                items.append(item_db.to_dict())

            result = {
                "total": total,
                "page_num": search_request.page_num,
                "page_size": search_request.page_size,
                "total_pages": total_pages,
                "items": items
            }

            logger.info(f"搜索导航页面完成 - 总数: {total}, 页数: {search_request.page_num}/{total_pages}")
            return result

        except Exception as e:
            logger.error(f"搜索导航页面失败 - 关键词: {search_request.keyword}, 场景IDs: {search_request.scene_ids}",
                         exc_info=True)
            raise Exception(f"搜索导航页面失败: {str(e)}") from e
        finally:
            db_session.close()

    @staticmethod
    async def create(data: AIWebApiCallingCreate) -> Optional[AIWebApiCallingResponse]:
        """创建导航页面（同时保存到数据库和ChromaDB）"""
        try:
            logger.info(f"创建导航页面 - 页面名称: {data.page_name}, 场景ID: {data.scene_id}")

            # 调用NavigationSaveService创建导航数据
            result = NavigationSaveService.create_navigation(data)

            if result:
                logger.info(f"成功创建导航页面 - ID: {result.page_id}")
            else:
                logger.warning("创建导航页面返回空结果")

            return result
        except Exception as e:
            logger.error(f"创建导航页面失败 - 页面名称: {data.page_name}, 场景ID: {data.scene_id}", exc_info=True)
            raise Exception(f"创建导航页面失败: {str(e)}") from e

    @staticmethod
    async def update_paths(page_id: str, path_data: AIWebApiCallingPathUpdate) -> Optional[AIWebApiCallingResponse]:
        """更新页面路径关系（同时更新数据库和ChromaDB）"""
        try:
            logger.info(f"更新页面路径关系 - 页面ID: {page_id}")

            # 构建更新数据
            update_data = AIWebApiCallingUpdate(
                prev_path_list=path_data.prev_path_list,
                next_path_list=path_data.next_path_list,
                updated_by=path_data.updated_by
            )

            # 调用NavigationSaveService更新导航数据
            result = NavigationSaveService.update_navigation(page_id, update_data)

            if result:
                logger.info(f"成功更新页面路径关系 - ID: {page_id}")
            else:
                logger.warning(f"更新页面路径关系返回空结果 - ID: {page_id}")

            return result
        except Exception as e:
            logger.error(f"更新页面路径关系失败 - 页面ID: {page_id}", exc_info=True)
            raise Exception(f"更新页面路径关系失败: {str(e)}") from e

    @staticmethod
    async def update(page_id: str, data: AIWebApiCallingUpdate) -> Optional[AIWebApiCallingResponse]:
        """更新导航页面（同时更新数据库和ChromaDB）"""
        try:
            logger.info(f"更新导航页面 - 页面ID: {page_id}")

            # 调用NavigationSaveService更新导航数据
            result = NavigationSaveService.update_navigation(page_id, data)

            if result:
                logger.info(f"成功更新导航页面 - ID: {page_id}")
            else:
                logger.warning(f"更新导航页面返回空结果 - ID: {page_id}")

            return result
        except Exception as e:
            logger.error(f"更新导航页面失败 - 页面ID: {page_id}", exc_info=True)
            raise Exception(f"更新导航页面失败: {str(e)}") from e

    @staticmethod
    async def delete(page_id: str, updated_by: str) -> bool:
        """软删除导航页面（同时处理数据库和ChromaDB）"""
        try:
            logger.info(f"删除导航页面 - 页面ID: {page_id}, 操作者: {updated_by}")

            # 调用NavigationSaveService删除导航数据
            result = NavigationSaveService.delete_navigation(page_id, updated_by)

            if result:
                logger.info(f"成功删除导航页面 - ID: {page_id}")
            else:
                logger.warning(f"删除导航页面失败 - ID: {page_id}")

            return result
        except Exception as e:
            logger.error(f"删除导航页面失败 - 页面ID: {page_id}, 操作者: {updated_by}", exc_info=True)
            raise Exception(f"删除导航页面失败: {str(e)}") from e

    @staticmethod
    async def batch_import(import_data: List[dict], import_mode: str, created_by: str) -> dict:
        """批量导入导航数据（同时保存到数据库和ChromaDB）"""
        try:
            logger.info(f"批量导入导航数据 - 数量: {len(import_data)}, 模式: {import_mode}, 操作者: {created_by}")

            success_count = 0
            failed_count = 0
            failed_items = []

            for i, item_data in enumerate(import_data):
                try:
                    # 构建创建数据
                    create_data = AIWebApiCallingCreate(
                        scene_id=item_data.get('scene_id').strip(),
                        tenant_id=item_data.get('tenant_id', 'default'),
                        page_name=item_data.get('page_name').strip(),
                        page_tab=item_data.get('page_tabs', []),
                        function_desc_list=item_data.get('function_desc_list', []),
                        operation_desc_list=item_data.get('operation_desc_list', []),
                        navigation_path=item_data.get('navigation_path').strip(),
                        prev_path_list=item_data.get('prev_path_list', []),
                        next_path_list=item_data.get('next_path_list', []),
                        mcp_list=item_data.get('mcp_list', []),
                        created_by=created_by
                    )

                    if import_mode in ['insert', 'upsert']:
                        # 插入模式或插入更新模式
                        result = NavigationSaveService.create_navigation(create_data)
                        if result:
                            success_count += 1
                        else:
                            failed_count += 1
                            failed_items.append({
                                "index": i + 1,
                                "data": item_data,
                                "error": "创建失败"
                            })
                    elif import_mode == 'update':
                        # 更新模式（需要先查找现有记录）
                        # 这里简化处理，实际应根据业务需求实现查找逻辑
                        failed_count += 1
                        failed_items.append({
                            "index": i + 1,
                            "data": item_data,
                            "error": "更新模式暂未实现"
                        })

                except Exception as item_error:
                    logger.error(f"导入第 {i + 1} 项数据失败", exc_info=True)
                    failed_count += 1
                    failed_items.append({
                        "index": i + 1,
                        "data": item_data,
                        "error": str(item_error)
                    })

            result = {
                "message": "导入完成",
                "total_count": len(import_data),
                "success_count": success_count,
                "failed_count": failed_count,
                "failed_items": failed_items,
                "import_mode": import_mode
            }

            logger.info(f"批量导入完成 - 总数: {len(import_data)}, 成功: {success_count}, 失败: {failed_count}")
            return result

        except Exception as e:
            logger.error(f"批量导入导航数据失败 - 数量: {len(import_data)}, 模式: {import_mode}", exc_info=True)
            raise Exception(f"批量导入导航数据失败: {str(e)}") from e

    @staticmethod
    async def search_similar_content(query: str, scene_id: str, limit: int = 10, 
                                   vector_weight: float = 0.7, bm25_weight: float = 0.3) -> List[dict]:
        """混合检索：根据内容搜索相似的导航数据（向量检索 + BM25）"""
        try:
            logger.info(f"混合检索相似内容 - 查询: {query}, 场景ID: {scene_id}, 限制: {limit}, "
                       f"向量权重: {vector_weight}, BM25权重: {bm25_weight}")

            # 调用NavigationSaveService进行混合检索
            results = NavigationSaveService.search_similar_content(
                query=query, 
                scene_id=scene_id, 
                limit=limit,
                vector_weight=vector_weight,
                bm25_weight=bm25_weight
            )

            logger.info(f"混合检索相似内容完成 - 结果数量: {len(results)}")
            return results
        except Exception as e:
            logger.error(f"混合检索相似内容失败 - 查询: {query}, 场景ID: {scene_id}", exc_info=True)
            raise Exception(f"混合检索相似内容失败: {str(e)}") from e
